package com.google.code.rgbgoesbetween.data.sqlite;

import java.sql.Date;

import com.google.code.rgbgoesbetween.data.AccountStatus;
import com.google.code.rgbgoesbetween.data.Profile;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteDBManager extends SQLiteOpenHelper {
	
	// Database info
	private static final String DATABASE_NAME = "rgbdatastore.db";
	private static final int DATABASE_VERSION = 1;
	
	// Need all of the tables here
	public static final String TABLE_ITEMS = "items";
	public static final String TABLE_PROFILES = "profiles";
	public static final String TABLE_REPORTS = "reports";
	
	// Need all of the item table keys here.
	public static final String COLUMN_ITEM_ID = "itemid";
	public static final String COLUMN_ITEM_NAME = "name";
	public static final String COLUMN_ITEM_TAGS = "tags";
	public static final String COLUMN_ITEM_LOCATION = "location";
	public static final String COLUMN_ITEM_DATERANGE = "date_range";
	public static final String COLUMN_ITEM_DESCRIPTION = "description";
	public static final String COLUMN_ITEM_REWARD = "reward";
	
	// Need all the profile table keys here
	public static final String COLUMN_PROFILE_ID = "profileid";
	public static final String COLUMN_PROFILE_USER = "username";
	public static final String COLUMN_PROFILE_PASS = "passhash";
	public static final String COLUMN_PROFILE_EMAIL = "email";
	public static final String COLUMN_PROFILE_PHONE = "phone_number";
	public static final String COLUMN_PROFILE_ADDRESS = "address";
	public static final String COLUMN_PROFILE_STATUS = "status";
	
	// Need all of the report table keys here
	public static final String COLUMN_REPORT_ID = "reportid";
	public static final String COLUMN_REPORT_ITEMID = "item_id";
	public static final String COLUMN_REPORT_REPORTERID = "reporter_id";

	// Database creation sql statement
	private static final String ITEMS_CREATE = 
			"CREATE TABLE " + TABLE_ITEMS + " (" + 
					COLUMN_ITEM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
					COLUMN_ITEM_NAME + " TEXT NOT NULL, " +
					COLUMN_ITEM_TAGS + " TEXT NOT NULL, " +
					COLUMN_ITEM_LOCATION + " TEXT NOT NULL, " +
					COLUMN_ITEM_DATERANGE + " TEXT NOT NULL, " +
					COLUMN_ITEM_DESCRIPTION + " TEXT NOT NULL, " +
					COLUMN_ITEM_REWARD + " TEXT NOT NULL" + 
			");";
	private static final String PROFILES_CREATE = "CREATE TABLE " + TABLE_PROFILES + " (" +
					COLUMN_PROFILE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					COLUMN_PROFILE_USER   + " TEXT NOT NULL, " +
					COLUMN_PROFILE_PASS   + " TEXT NOT NULL, " +
					COLUMN_PROFILE_EMAIL  + " TEXT , " +
					COLUMN_PROFILE_PHONE  + " TEXT , " +
					COLUMN_PROFILE_ADDRESS+ " TEXT , " +
					COLUMN_PROFILE_STATUS + " INTEGER NOT NULL" +
			");";
	private static final String REPORTS_CREATE = "CREATE TABLE " + TABLE_REPORTS + " (" +
					COLUMN_REPORT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
					COLUMN_REPORT_ITEMID 	 + " REFERENCES " + TABLE_ITEMS +
						"(" + COLUMN_ITEM_ID + "), " +  
					COLUMN_REPORT_REPORTERID + " REFERENCES " + TABLE_PROFILES +
						"(" + COLUMN_PROFILE_ID + ")" +
			");";
	 
	public SQLiteDBManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		if (!db.isReadOnly()) {
	        // Enable foreign key constraints
	        db.execSQL("PRAGMA foreign_keys=ON;");
	    }
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(ITEMS_CREATE);
		db.execSQL(PROFILES_CREATE);
		db.execSQL(REPORTS_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Definitely need something more clever here to change the schema, but this will
		// work as a placeholder.
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ITEMS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROFILES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_REPORTS);
	    onCreate(db);
	}

}
